﻿CREATE TABLE [dbo].[TimeDivisions] (
    [TimeDivID]           INT            IDENTITY (1000, 1) NOT NULL,
    [TimeDivType]         NVARCHAR (255) NOT NULL,
    [ShortName]           NVARCHAR (10)  NOT NULL,
    [StartDate]           DATETIME       NULL,
    [EndDate]             DATETIME       NULL,
    [InstitutionEntityID] INT            CONSTRAINT [DF_TimeDivisions_InstitutionEntityID] DEFAULT ((0)) NOT NULL,
    [SuperTimeDivID]      INT            NULL,
    [Name]                NVARCHAR (255) NOT NULL,
    [Description]         NVARCHAR (MAX) NULL,
    [ExternalID]          INT            NULL,
    [Sequence]            INT            NULL,
    CONSTRAINT [PK_TimeDivisions] PRIMARY KEY CLUSTERED ([TimeDivID] ASC),
    CONSTRAINT [FK_TimeDivisions_DefinedTimeDivisions] FOREIGN KEY ([TimeDivType]) REFERENCES [dbo].[DefinedTimeDivisions] ([TimeDivType]),
    CONSTRAINT [FK_TimeDivisions_Entity] FOREIGN KEY ([InstitutionEntityID]) REFERENCES [dbo].[Entities] ([EntityID]),
    CONSTRAINT [FK_TimeDivisions_TimeDivisions] FOREIGN KEY ([SuperTimeDivID]) REFERENCES [dbo].[TimeDivisions] ([TimeDivID])
);




GO
CREATE UNIQUE NONCLUSTERED INDEX [ShortName_Unique]
    ON [dbo].[TimeDivisions]([ShortName] ASC);


GO
CREATE UNIQUE NONCLUSTERED INDEX [Name_Unique]
    ON [dbo].[TimeDivisions]([Name] ASC);

